﻿create PROCEDURE Sync.SP_UserMessages_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @UserMessages_Temp TABLE 
		(
			UserMessageID UniqueIdentifier,
			[CreationDate] datetime,
				[FromUserID] uniqueidentifier,
				[ToUserID] uniqueidentifier,
				[Subject] nvarchar(255),
				[Message] nvarchar(MAX),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @UserMessages_Temp 
	   SELECT	Tbl.Col.value('UserMessageID[1]','uniqueidentifier') UserMessageID,
                Tbl.Col.value('CreationDate[1]', 'datetime') [CreationDate],
Tbl.Col.value('FromUserID[1]', 'uniqueidentifier') [FromUserID],
Tbl.Col.value('ToUserID[1]', 'uniqueidentifier') [ToUserID],
Tbl.Col.value('Subject[1]', 'nvarchar(255)') [Subject],
Tbl.Col.value('Message[1]', 'nvarchar(MAX)') [Message],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/UserMessages') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[UserMessages] ([UserMessageID],[CreationDate],[FromUserID],[ToUserID],[Subject],[Message])
	SELECT t.[UserMessageID],t.[CreationDate],t.[FromUserID],t.[ToUserID],t.[Subject],t.[Message]
	FROM  @UserMessages_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.UserMessages_Tracking s
        WHERE t.UserMessageID = s.UserMessageID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[UserMessages]
	SET [UserMessages].[CreationDate] = t.[CreationDate],[UserMessages].[FromUserID] = t.[FromUserID],[UserMessages].[ToUserID] = t.[ToUserID],[UserMessages].[Subject] = t.[Subject],[UserMessages].[Message] = t.[Message] 
	FROM [dbo].[UserMessages] s JOIN @UserMessages_Temp t 
    ON t.UserMessageID = s.UserMessageID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.UserMessages_Tracking r
				  WHERE t.UserMessageID = r.UserMessageID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[UserMessages]
    FROM @UserMessages_Temp t JOIN [dbo].[UserMessages] s
    ON t.UserMessageID = s.UserMessageID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END





